import re

import pandas as pd
import numpy as np
from sqlalchemy import and_
from datetime import datetime
from app.services import get_base_session as get_session
from app.utils.common_func_defs import *
from app.models.data_base_models import *

# 从数据库取数
def get_data_from_mysql(session, begin_date: str, end_date: str, table_class, varlist: list = []):
    query = session.query(table_class).filter(and_(table_class.stat_time >= begin_date,
                                                   table_class.stat_time <= end_date))
    df = pd.read_sql(query.statement, session.bind)
    if varlist:
        df = df[varlist]
        return df
    else:
        return df


# 展示上传模块中对应数据库表内容
def display_uploaded0(df, table_zh_name:str):
    df = uploaded_field_corr_entozh_res(df, table_zh_name)
    df = df.fillna('nan')
    return df

######################################################## 方法定义 #######################################################


# 获取电商情况表
def get_online_complement(brand_name: str, stat_time: str, filter_brand: str, aggregate: str):
    # 提取所需要的年和月
    stat_time = datetime.strptime(stat_time, '%Y-%m')
    # year = stat_time.year
    # month = stat_time.month

    # 读取数据
    session = get_session()
    # 计划表（对其店铺和平台去重得到要计算的df）
    query_plan = session.query(plan_month)
    df_plan = pd.read_sql(query_plan.statement, session.bind)
    df_plan = df_plan.fillna(np.nan)  # 将所有None转化为nan，避免运算时报错
    df_plan = df_plan[
        ['stat_time', 'customer_code', 'store_name', 'platform_name', 'attribute', 'brand_name', 'plan_amount']]

    if df_plan.empty:
        return None

    # 发货销售表
    query_sale = session.query(deliver_sale_month)
    df_sale = pd.read_sql(query_sale.statement, session.bind)
    df_sale = df_sale.fillna(np.nan)  # 将所有None转化为nan，避免运算时报错
    df_sale = df_sale[['stat_time', 'material_code', 'customer_code', 'deliver_amount', 'A_price_amount']]
    # 产品信息表
    query_product = session.query(product_information)
    df_product = pd.read_sql(query_product.statement, session.bind)
    df_product = df_product.fillna(np.nan)  # 将所有None转化为nan，避免运算时报错
    df_product = df_product[['material_code', 'brand_name', 'parent_series', 'child_series']]
    # 客户信息表
    query_custormer = session.query(customer_information)
    df_custormer = pd.read_sql(query_custormer.statement, session.bind)
    df_custormer = df_custormer.fillna(np.nan)  # 将所有None转化为nan，避免运算时报错
    df_custormer = df_custormer[['customer_code', 'store_name', 'attribute']]

    # 生成达成情况表--计划部分
    df = df_plan.groupby(['platform_name', 'store_name', 'stat_time', 'brand_name'], as_index=False).agg(
        {'plan_amount': np.sum}).reset_index(drop=True)  # 得到计划量

    # 将发货销售表和产品、客户表匹配，得到完整的发货销售表
    df_sale = pd.merge(df_sale, df_product, on=['material_code'], how='left')
    df_sale = pd.merge(df_sale, df_custormer, on=['customer_code'], how='left')

    # 匹配计算实际销售量
    df_sale = df_sale[['stat_time', 'material_code', 'customer_code', 'deliver_amount', 'A_price_amount',
                       'brand_name', 'parent_series', 'child_series', 'store_name', 'attribute']]
    df = pd.merge(df, df_sale, on=['store_name', 'stat_time', 'brand_name'], how='left')
    df = df.groupby(['platform_name', 'store_name', 'stat_time', 'brand_name', 'plan_amount', 'attribute'],
                         as_index=False).agg(
        {'deliver_amount': np.sum, 'A_price_amount': np.sum}).reset_index(drop=True)  # 得到两种计划量，下面筛选
    df['actual_amount'] = df.apply(
        lambda row: (row['A_price_amount'] / 10000) if row['attribute'] == '实销' else (row['deliver_amount'] / 10000),
        axis=1)  # 注意这里求和之后根据excel计算逻辑，要除以10000

    # 表内计算：本月完成率、本月差异、月度排名
    df['complement_rate'] = df['actual_amount'] / df['plan_amount']
    df['difference_amount'] = df['actual_amount'] - df['plan_amount']
    df['rank'] = df.groupby(['stat_time', 'brand_name'])['complement_rate'].rank(ascending=False)  # 添加排名（同一品牌、月份下）

    # 计算累计指标
    if df['stat_time'] is None or df['stat_time'].empty:
        raise Exception('stat_time is empty')
    df['stat_time'] = df['stat_time'].apply(lambda x: x if re.match(pattern = r'^\d{4}-\d{2}-\d{2}$', string=x) else x+"-01")
    df['stat_time'] = pd.to_datetime(df['stat_time'])  # 比较稳健地将数据转化为日期格式

    if df.empty:
        df['cumulative_plan_amount'] = np.nan
    else:
        df['cumulative_plan_amount'] = df.groupby([df['stat_time'].dt.year, 'platform_name', 'store_name', 'brand_name'])[
        'plan_amount'].cumsum()

    if df.empty:
        df['cumulative_actual_amount'] = np.nan
    else:
        df['cumulative_actual_amount'] = df.groupby([df['stat_time'].dt.year, 'platform_name', 'store_name', 'brand_name'])[
        'actual_amount'].cumsum()

    df['cumulative_complement_rate'] = df['cumulative_actual_amount'] / df['cumulative_plan_amount']
    df['cumulative_difference_amount'] = df['cumulative_actual_amount'] - df['cumulative_plan_amount']
    df['cumulative_rank'] = df.groupby(['stat_time', 'brand_name'])['cumulative_complement_rate'].rank(
        ascending=False)  # 添加排名（同一品牌、月份下）

    df.replace([np.inf, -np.inf], 0, inplace=True)  # 替换除数为0的inf

    # 根据筛选输入的要求，进行筛选和返回
    df = df[(df['stat_time'] == stat_time)]  # 先筛选月份

    if aggregate == '是':
        if filter_brand == '是':
            df = df[(df['brand_name'] == brand_name)]  # 筛选品牌
        # 下面按平台累计
        df = df.groupby(['platform_name', 'stat_time', 'brand_name'],
                             as_index=False).agg(
            {'actual_amount': np.sum, 'plan_amount': np.sum, 'cumulative_actual_amount': np.sum,
             'cumulative_plan_amount': np.sum}).reset_index(drop=True)  # 聚合累加的项
        # 表内计算：本月完成率、本月差异、月度排名
        df['complement_rate'] = df['actual_amount'] / df['plan_amount']
        df['difference_amount'] = df['actual_amount'] - df['plan_amount']
        df['rank'] = df.groupby(['stat_time', 'brand_name'])['complement_rate'].rank(
            ascending=False)  # 添加排名（同一品牌、月份下）
        df['cumulative_complement_rate'] = df['cumulative_actual_amount'] / df['cumulative_plan_amount']
        df['cumulative_difference_amount'] = df['cumulative_actual_amount'] - df['cumulative_plan_amount']
        df['cumulative_rank'] = df.groupby(['stat_time', 'brand_name'])['cumulative_complement_rate'].rank(
            ascending=False)  # 添加排名（同一品牌、月份下）
        df = df[['stat_time', 'brand_name', 'platform_name', 'plan_amount', 'actual_amount',
                 'complement_rate', 'difference_amount', 'rank',
                 'cumulative_plan_amount', 'cumulative_actual_amount',
                 'cumulative_complement_rate', 'cumulative_difference_amount',
                 'cumulative_rank']]  # 筛选所需字段（重新排序）

        df.replace([np.inf, -np.inf], 0, inplace=True)  # 替换除数为0的inf

        # 调整数字格式（整数和小数）
        df['stat_time'] = df['stat_time'].dt.strftime('%Y-%m')
        df['plan_amount'] = df['plan_amount'].round(2)
        df['actual_amount'] = df['actual_amount'].round(2)
        df['complement_rate'] = df['complement_rate'].round(2)
        df['difference_amount'] = df['difference_amount'].round(2)
        df['rank'] = df['rank'].round(0)
        df['cumulative_plan_amount'] = df['cumulative_plan_amount'].round(2)
        df['cumulative_actual_amount'] = df['cumulative_actual_amount'].round(2)
        df['cumulative_complement_rate'] = df['cumulative_complement_rate'].round(2)
        df['cumulative_difference_amount'] = df['cumulative_difference_amount'].round(2)
        df['cumulative_rank'] = df['cumulative_rank'].round(0)

        # 转中文字段名
        df = uploaded_field_corr_entozh_res(df, '电商达成情况合计月统计表')
        df = df.fillna('nan')

        return df
    else:
        if filter_brand == '是':
            df = df[(df['brand_name'] == brand_name)]  # 筛选品牌
        df = df[['stat_time', 'brand_name', 'platform_name', 'store_name', 'plan_amount', 'actual_amount',
       'complement_rate', 'difference_amount', 'rank',
       'cumulative_plan_amount', 'cumulative_actual_amount',
       'cumulative_complement_rate', 'cumulative_difference_amount',
       'cumulative_rank']]  # 筛选所需字段
        # 不用按平台累计，直接可输出

        # 调整数字格式（整数和小数）
        df['stat_time'] = df['stat_time'].dt.strftime('%Y-%m')
        df['plan_amount'] = df['plan_amount'].round(2)
        df['actual_amount'] = df['actual_amount'].round(2)
        df['complement_rate'] = df['complement_rate'].round(2)
        df['difference_amount'] = df['difference_amount'].round(2)
        df['rank'] = df['rank'].round(0)
        df['cumulative_plan_amount'] = df['cumulative_plan_amount'].round(2)
        df['cumulative_actual_amount'] = df['cumulative_actual_amount'].round(2)
        df['cumulative_complement_rate'] = df['cumulative_complement_rate'].round(2)
        df['cumulative_difference_amount'] = df['cumulative_difference_amount'].round(2)
        df['cumulative_rank'] = df['cumulative_rank'].round(0)

        # 转中文字段名
        df = uploaded_field_corr_entozh_res(df, '电商达成情况月统计表')
        df = df.fillna('nan')

        return df